package com.aplink.generic.database;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;

import com.aplink.generic.constant.SqlDataType;
import com.aplink.generic.pojo.GenericAttribute;
import com.aplink.generic.pojo.GenericObject;
import com.aplink.generic.pojo.JoinCol;
import com.aplink.generic.pojo.ObjPropCol;
import com.aplink.generic.pojo.ObjPropInfo;
import com.aplink.generic.util.LogUtil;
import com.aplink.generic.util.Utility;
import com.aplink.generic.util.convertcount.GenericConverter;
import com.aplink.generic.util.convertcount.GenericFilter;

import java.util.ArrayList;
import java.util.Date;

public class DatabaseHelper implements ISaveCommand, ISelectCommand,
        IUpdateCommand {
    private static final int FAILED = - 1;
    private static final int GET_ALL = - 1;
    private SQLiteDatabase mDatabase;
    private DatabaseManager mDatabaseHelper;
    private final LogUtil mLogUtil = new LogUtil( this.getClass() );

    public DatabaseHelper( DatabaseManager databaseManager ) {
        mDatabaseHelper = databaseManager;
    }

    private ArrayList<GenericObject> baseGet( ObjPropCol objPropCol,
                                              String whereClause, int limitRecord ) {
        try {
            if ( objPropCol == null || objPropCol.isEmpty() ) {
                return null;
            }
            ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
            SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
            String tableName = objPropCol.getPrimaryKeyObject()
                    .getObjectMapping();
            String[] queriedColumns = new String[objPropCol.size()];

            for ( int i = 0 ; i < objPropCol.size() ; i++ ) {
                ObjPropInfo objPropInfo = objPropCol.get( i );
                queriedColumns[i] = objPropInfo.getObjectMapping() + '.'
                        + objPropInfo.getObjectProperty();
            }
            String limit = limitRecord != GET_ALL ? String.valueOf( limitRecord )
                    : null;

            JoinCol joins = objPropCol.getJoins();

            Cursor cursor;
            beginReadDatabase();

            if ( joins == null || joins.size() == 0 ) {
                cursor = mDatabase.query( tableName, queriedColumns,
                        whereClause, null, null, null,
                        objPropCol.getOderByClause(), limit );
            } else {
                queryBuilder.setTables( joins.GetJoinString() );
                cursor = queryBuilder.query( mDatabase, queriedColumns,
                        whereClause, null, null, null,
                        objPropCol.getOderByClause(), limit );
                String queryString = SQLiteQueryBuilder.buildQueryString( false,
                        joins.GetJoinString(), queriedColumns, whereClause,
                        null, null, objPropCol.getOderByClause(), limit );
                mLogUtil.info( queryString, "buildQueryString" );
            }

            if ( cursor != null ) {
                cursor.moveToFirst();
                int numRows = cursor.getCount();
                for ( int i = 0 ; i < numRows ; ++ i ) {
                    GenericObject genericObject = new GenericObject();
                    genericObject.setObjectMappingName( tableName );
                    ArrayList<GenericAttribute<?, ?>> attributeList = new ArrayList<GenericAttribute<?, ?>>();
                    for ( int j = 0 ; j < objPropCol.size() ; j++ ) {
                        ObjPropInfo objPropInfo = objPropCol.get( j );
                        String name = objPropInfo.getObjectProperty();
                        SqlDataType dataType = objPropInfo.getDataType();
                        String value = cursor.getString( cursor
                                .getColumnIndex( name ) );
                        GenericAttribute<?, ?> attribute = GenericConverter
                                .getGenericAttribute( name, dataType, value );
                        attributeList.add( attribute );
                    }
                    genericObject.setAttributeList( attributeList );
                    genericObjectList.add( genericObject );
                    cursor.moveToNext();
                }
                cursor.close();
                cursor = null;
                return genericObjectList;
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        } finally {
            closeDatabase();
        }
        return new ArrayList<GenericObject>();
    }

    private long baseInsert( GenericObject object, ObjPropCol objPropCol ) {
        String mainTable = object.getObjectMappingName();
        ObjPropCol arrMainObjPropInfo = GenericFilter.getObjPropListMainTable(
                mainTable, objPropCol );
        for ( ObjPropInfo objPropInfo : arrMainObjPropInfo ) {
            // Insert to primary table
            if ( objPropInfo.isFKey() ) {
                String attributeName = objPropInfo.getObjectProperty();
                String primaryTableName = objPropInfo.getObjectMapping();
                ObjPropCol primaryTable = GenericFilter
                        .getObjPropListMainTable( primaryTableName, objPropCol );
                ContentValues cv = generateContentValues(
                        object.getAttributeList(), primaryTable );
                long idPrimary = mDatabase.insert( primaryTableName, null, cv );
                if ( idPrimary != FAILED ) {
                    object.setContentByAttributeName( attributeName, idPrimary );
                } else {
                    mLogUtil.error( String.format(
                            "%s with content values: %s is exists",
                            primaryTableName, cv.toString() ),
                            "Base insert object", new SQLException() );
                    throw new SQLException();
                }
            }
        }
        try {
            ContentValues cv = generateContentValues( object.getAttributeList(),
                    arrMainObjPropInfo );
            return mDatabase.insert( object.getObjectMappingName(), null, cv );
        } catch ( SQLException e ) {
            throw e;
        }
    }

    private void beginReadDatabase() {
        if ( mDatabase == null || ! mDatabase.isOpen() ) {
            mDatabase = mDatabaseHelper.getReadableDatabase();
        }
    }

    private void beginWriteDatabase() {
        if ( mDatabase == null || ! mDatabase.isOpen() ) {
            mDatabase = mDatabaseHelper.getWritableDatabase();
            mDatabase.beginTransaction();
        }
    }

    private void closeDatabase() {
        if ( mDatabase != null && mDatabase.isOpen() ) {
            if ( mDatabase.inTransaction() ) {
                mDatabase.setTransactionSuccessful();
                mDatabase.endTransaction();
            }
            mDatabaseHelper.close();
        }
    }

    private ContentValues generateContentValues(
            ArrayList<GenericAttribute<?, ?>> genericAttributes,
            ArrayList<ObjPropInfo> fTable ) {
        ContentValues cv = new ContentValues();
        for ( int i = 0 ; i < genericAttributes.size() ; i++ ) {
            String attributeId = null;
            String value = null;
            if ( genericAttributes.get( i ).getName() != null ) {
                attributeId = genericAttributes.get( i ).getName().toString();
            }

            if ( genericAttributes.get( i ).getValue() != null ) {
                if ( genericAttributes.get( i ).getValue() instanceof Date ) {
                    value = Utility.formatDatetime( (Date) genericAttributes
                            .get( i ).getValue() );
                } else {
                    value = genericAttributes.get( i ).getValue().toString();
                }
            }

            final boolean isInsidePropInfo = GenericFilter
                    .checkAttributeInsideObjPropInfoList( attributeId, fTable );
            if ( isInsidePropInfo == true ) {
                cv.put( attributeId, value );
                if ( cv.size() == fTable.size() ) {
                    break;
                }
            }
        }
        return cv;
    }

    @Override
    public GenericObject get( ObjPropCol propCol, String whereClause ) {
        if ( propCol == null || propCol.isEmpty() ) {
            return null;
        }
        try {
            beginReadDatabase();
            return baseGet( propCol, whereClause, 1 ).get( 0 );
        } catch ( SQLException e ) {
            e.printStackTrace();
        } finally {
            closeDatabase();
        }
        return null;
    }

    @Override
    public ArrayList<GenericObject> getList( ObjPropCol totalObjPropList,
                                             String whereClause ) {
        return baseGet( totalObjPropList, whereClause, GET_ALL );
    }

    @Override
    public long insert( GenericObject object, ObjPropCol objPropCol ) {
        if ( object.getAttributeList().size() == 0 ) {
            return 0;
        }
        long id = FAILED;
        try {
            beginWriteDatabase();
            id = baseInsert( object, objPropCol );
            if ( id != FAILED ) {
                mLogUtil.info( object.getObjectMappingName()
                        + " was inserted, id=" + id, "insertObject" );
            } else {
                mLogUtil.info( " can't insert " + object.getObjectMappingName()
                        + "  to database", "insertObject" );
            }
            return id;
        } catch ( SQLException e ) {
            e.printStackTrace();
        } finally {
            closeDatabase();
        }
        return FAILED;
    }

    @Override
    public ArrayList<Long> insertList( ArrayList<GenericObject> genericObjects,
                                       ObjPropCol objPropCol ) {
        ArrayList<Long> ids = new ArrayList<Long>();
        if ( objPropCol.size() == 0 || genericObjects.size() == 0 ) {
            return null;
        }
        try {
            beginWriteDatabase();
            for ( GenericObject object : genericObjects ) {
                long id = baseInsert( object, objPropCol );
                if ( id != FAILED ) {
                    ids.add( id );
                    mLogUtil.info( object.getObjectMappingName()
                            + " was inserted, id=" + id, "insertObject" );
                } else {
                    mLogUtil.info(
                            "Can't not insert " + object.getObjectMappingName(),
                            "insertObject" );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        } finally {
            closeDatabase();
        }
        return new ArrayList<Long>();
    }

    @Override
    public long insertOrUpdate( GenericObject object, ObjPropCol objPropCol ) {
        boolean isExists = isExists( object, objPropCol );
        if ( isExists ) {
            return update( object, objPropCol );
        } else {
            return insert( object, objPropCol );
        }
    }

    @Override
    public boolean isExists( GenericObject genericObject, ObjPropCol objPropCol ) {
        String mainTable = genericObject.getObjectMappingName();

        ObjPropInfo objPrimaryKey = null;
        for ( ObjPropInfo info : objPropCol ) {
            if ( info.isKey() ) {
                objPrimaryKey = info;
                break;
            }
        }

        if ( objPrimaryKey != null ) {
            beginReadDatabase();
            SqlDataType dataType = objPrimaryKey.getDataType();
            String column = objPrimaryKey.getObjectProperty();
            String value = genericObject.getContentFromAttributeName( column );
            String whereCondition = SqlMethods.getInstance().equal( dataType, column,
                    value ).toString();
            SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
            queryBuilder.setTables( mainTable );
            queryBuilder.appendWhere( whereCondition );
            try {
                Cursor cursor = queryBuilder.query( mDatabase, null, null, null,
                        null, null, null, "1" );
                return cursor.getCount() == 1;
            } catch ( SQLException e ) {
                e.printStackTrace();

            } finally {
                closeDatabase();
            }
        }

        return false;
    }

    @Override
    public boolean isExists( ObjPropCol totalObjPropList, String whereClause ) {

        if ( totalObjPropList.size() == 0 ) {
            return false;
        }

        if ( ! whereClause.isEmpty() ) {
            beginReadDatabase();
            SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
            JoinCol joinCol = totalObjPropList.getJoins();
            String table = null;
            if ( joinCol != null ) {
                table = joinCol.GetJoinString();
            } else {
                table = totalObjPropList.get( 0 ).getObjectMapping();
            }
            queryBuilder.setTables( table );
            queryBuilder.appendWhere( whereClause );
            try {
                Cursor cursor = queryBuilder.query( mDatabase, null, null, null,
                        null, null, null, "1" );
                return cursor.getCount() == 1;
            } catch ( SQLException e ) {
                e.printStackTrace();
            } finally {
                closeDatabase();
            }
        }

        return false;
    }

    @Override
    public long update( GenericObject object, ObjPropCol objPropCol ) {
        if ( object.getAttributeList().size() <= 0 ) {
            return - 1;
        }
        try {
            beginWriteDatabase();
            String mainTable = object.getObjectMappingName();
            ObjPropCol mainObjPro = GenericFilter.getObjPropListMainTable(
                    mainTable, objPropCol );
            SqlDataType dataType = mainObjPro.getPrimaryKeyObject()
                    .getDataType();
            String column = mainObjPro.getPrimaryKeyObject()
                    .getObjectProperty();
            String value = object.getContentFromAttributeName( column );
            String whereCondition = SqlMethods.getInstance().equal( dataType, column,
                    value ).toString();
            ContentValues contentValues = generateContentValues(
                    object.getAttributeList(), mainObjPro );
            int numberRowEffected = mDatabase.update( mainTable, contentValues,
                    whereCondition, null );
            if ( numberRowEffected > 0 ) {
                mLogUtil.info( String.format(
                        "successful in insert %d %s record(s)",
                        numberRowEffected, mainTable ), "update" );
                return numberRowEffected;
            }
            mLogUtil.info( mainTable + " was update faild", "update" );
            return FAILED;
        } catch ( SQLException e ) {
            e.printStackTrace();
        } finally {
            closeDatabase();
        }
        return 0;
    }

    @Override
    public long update( GenericObject object, ObjPropCol objPropCol,
                        String whereClause ) {
        return 0;
    }

    @Override
    public long updateList( ArrayList<GenericObject> objects,
                            ObjPropCol objPropCol ) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public long updateList( ArrayList<GenericObject> objects,
                            ObjPropCol objPropCol, String whereClause ) {
        return 0;
    }
}
